package com.yeziji.devops.sql.builder;

import cn.hutool.core.collection.CollectionUtil;
import cn.hutool.core.util.StrUtil;
import cn.hutool.extra.spring.SpringUtil;
import com.yeziji.common.CommonSymbol;
import com.yeziji.constant.VariousStrPool;
import com.yeziji.devops.common.SqlBuilder;
import com.yeziji.devops.common.expcetion.SqlBuilderException;
import com.yeziji.devops.common.msg.SqlBuilderErrorMsg;
import com.yeziji.devops.constant.SqlExecuteTypeEnum;
import com.yeziji.devops.constant.mysql.MysqlConstraintTypeEnum;
import com.yeziji.devops.constant.mysql.MysqlKeywordEnum;
import com.yeziji.devops.constant.mysql.MysqlMemoryEngineEnum;
import com.yeziji.devops.sql.DevopsJdbc;
import com.yeziji.devops.sql.base.SqlBuilderBase;
import com.yeziji.devops.sql.constructor.AlterSqlConstructor;
import com.yeziji.devops.sql.info.AlterAddInfo;
import com.yeziji.devops.sql.info.AlterDropInfo;
import com.yeziji.devops.sql.info.AlterEditInfo;
import com.yeziji.devops.sql.info.IndexKeyInfo;
import com.yeziji.devops.sql.model.mysql.SqlColumn;

import java.util.Collections;
import java.util.List;
import java.util.Optional;
import java.util.StringJoiner;

/**
 * alter ddl 构建器
 *
 * <p>该 sqlJoiner 根据列表数据构建多行 ddl 语句，进行统一执行
 *
 * @author gzkemays
 * @since 2023/01/27 2:56 PM
 */
public class AlterBuilder extends SqlBuilderBase<AlterSqlConstructor> implements SqlBuilder {
    public AlterBuilder(AlterSqlConstructor constructor) {
        super(constructor);
        super.sqlJoiner = new StringJoiner("\n");
    }

    @Override
    public String execute() {
        // 具备 add 语句
        if (CollectionUtil.isNotEmpty(constructor.getAdd())) {
            for (AlterAddInfo alterAddInfo : constructor.getAdd()) {
                // 字段类型不为空则认为需要构建 add column
                if (StrUtil.isNotBlank(alterAddInfo.getColumnType())) {
                    sqlJoiner.add(this.buildAddColumnDDl(alterAddInfo));
                }
                // 外键的引用以及关联不为空则认为需要构建外键
                if (CollectionUtil.isNotEmpty(alterAddInfo.getReferencesColumnName())) {
                    sqlJoiner.add(this.buildForeignDDl(alterAddInfo));
                }
                // 判断是否存在约束
                if (StrUtil.isNotBlank(alterAddInfo.getConstraintColumnName()) &&
                        StrUtil.isNotBlank(alterAddInfo.getConstraintType())) {
                    sqlJoiner.add(this.buildConstraintDDl(alterAddInfo));
                }
            }
        }
        // 具备 drop 语句
        else if (CollectionUtil.isNotEmpty(constructor.getDrop())) {
            for (AlterDropInfo dropInfo : constructor.getDrop()) {
                sqlJoiner.add(this.buildDropDDl(dropInfo));
            }
        }
        // 具备 modify 语句
        else if (CollectionUtil.isNotEmpty(constructor.getEdit())) {
            for (AlterEditInfo alterEditInfo : constructor.getEdit()) {
                // 修改表信息
                if (alterEditInfo.isEditTableInfo()) {
                    sqlJoiner.add(this.buildEditTableDDl(alterEditInfo));
                }
                // 修改字段信息
                if (StrUtil.isNotBlank(alterEditInfo.getColumnName())) {
                    // change 操作，主要做变更名字，后续其他操作交由 modify 实现
                    if (StrUtil.isNotBlank(alterEditInfo.getColumnRename())) {
                        sqlJoiner.add(this.buildChangeDDl(alterEditInfo));
                    }
                    // 判断是否需要 modify，如果需要的话再另外处理
                    if (alterEditInfo.isModifyColumnInfo()) {
                        sqlJoiner.add(this.buildModifyDDl(alterEditInfo));
                    }
                }
                // 修改表名称
                if (StrUtil.isNotBlank(alterEditInfo.getTableRename())) {
                    sqlJoiner.add(this.buildReTableDDl(alterEditInfo));
                }
            }
        }
        // 具备 index 语句
        else if (CollectionUtil.isNotEmpty(constructor.getIndex())) {
            for (IndexKeyInfo index : constructor.getIndex()) {
                if (CollectionUtil.isNotEmpty(index.getFields())) {
                    sqlJoiner.add(this.buildIndexDDl(index));
                }
            }
        }
        return sqlJoiner.toString();
    }

    /**
     * 构建 create index
     *
     * <p>CREATE INDEX test10_testname_IDX USING BTREE ON testdata.test10 (testname);
     *
     * <p>CREATE FULLTEXT INDEX test10_testname_IDX ON testdata.test10 (testname);
     *
     * @param info 索引键信息
     * @return {@link String} create index ddl
     */
    private String buildIndexDDl(IndexKeyInfo info) {
        String indexMethod = info.getIndexMethod();
        boolean isFullText = indexMethod != null && indexMethod.equals(MysqlKeywordEnum.FULL_TEXT.getValue());
        StringJoiner sj = new StringJoiner(CommonSymbol.SPACE, VariousStrPool.EMPTY, MysqlKeywordEnum.END.getValue());
        sj.add(SqlExecuteTypeEnum.CREATE.getValue());
        // 创建的索引类型
        if (isFullText) {
            sj.add(MysqlKeywordEnum.FULL_TEXT.getValue());
        }
        sj.add(MysqlKeywordEnum.INDEX.getValue());
        // 没有自定义名称，则自动生成
        String indexName = info.getName();
        List<String> fields = info.getFields();
        if (StrUtil.isBlank(indexName)) {
            indexName = super.getTable() + "_" + StrUtil.join(CommonSymbol.COMMA, fields).replace(CommonSymbol.COMMA, "_") + "IDX";
            info.setName(indexName);
        }
        sj.add(indexName);
        if (!isFullText) {
            sj.add(MysqlKeywordEnum.USING.getValue())
                    .add(Optional.ofNullable(indexMethod).orElse(MysqlKeywordEnum.BTREE.getValue()));
        }
        sj.add(MysqlKeywordEnum.ON.getValue()).add(getTable()).add(MysqlKeywordEnum.brackets(fields, true));
        // 追加 comment
        if (StrUtil.isNotBlank(info.getComment())) {
            sj.add(MysqlKeywordEnum.comment(info.getComment()));
        }
        return sj.toString();
    }

    /**
     * 构建 drop ddl 语句
     *
     * <ol>
     *   主要实现的数据参数，默认固定前缀：ALTER TABLE XXX DROP ...
     *   <li>0 删除约束：CONSTRAINT test6_check;
     *   <li>1 删除外键：FOREIGN KEY test3_fk_id2_id;
     *   <li>2 删除索引：INDEX test3_fk_id2_id;
     *   <li>3 删除字段：COLUMN id2;
     * </ol>
     *
     * @param alterDropInfo 删除信息
     * @return {@link String} DROP DDL 语句
     */
    private String buildDropDDl(AlterDropInfo alterDropInfo) {
        StringJoiner dropPrefixJoiner = this.getDropPrefixJoiner();
        switch (alterDropInfo.getMode()) {
            case 0:
                dropPrefixJoiner.add(MysqlKeywordEnum.COLUMN.getValue());
                break;
            case 1:
                dropPrefixJoiner.add(MysqlKeywordEnum.KEY.getValue());
                break;
            case 2:
                dropPrefixJoiner.add(MysqlKeywordEnum.CONSTRAINT.getValue());
                break;
            case 3:
                dropPrefixJoiner.add(MysqlKeywordEnum.FOREIGN_KEY.getValue());
                break;
            case 4:
                dropPrefixJoiner.add(MysqlKeywordEnum.INDEX.getValue());
                break;
            default:
        }
        dropPrefixJoiner.add(alterDropInfo.getName());
        return dropPrefixJoiner.toString();
    }

    /**
     * 构建 ALTER TABLE xxx
     *
     * <p>ALTER TABLE XXX DEFAULT CHARSET=geostd8 COLLATE=geostd8_bin COMMENT='32' AUTO_INCREMENT=0
     *
     * @param alterEditInfo 修改信息
     * @return {@link String} RENAME TABLE xxx TO XXX ddl 语句
     */
    private String buildEditTableDDl(AlterEditInfo alterEditInfo) {
        StringJoiner alterPrefixJoiner = this.getAlterPrefixJoiner();
        // 构建 ENGINE=XXX
        if (StrUtil.isNotBlank(alterEditInfo.getTableEngine())) {
            MysqlMemoryEngineEnum engineEnum =
                    MysqlMemoryEngineEnum.getByValue(alterEditInfo.getTableEngine());
            if (engineEnum != null) {
                alterPrefixJoiner.add(MysqlKeywordEnum.editKeyword(MysqlKeywordEnum.ENGINE, engineEnum.getValue()));
            } else {
                throw new SqlBuilderException(SqlBuilderErrorMsg.NOT_SUPPORT_ENGINE);
            }
        }
        // 构建 DEFAULT CHARSET=XXX
        if (StrUtil.isNotBlank(alterEditInfo.getTableCharset())) {
            alterPrefixJoiner.add(MysqlKeywordEnum.DEFAULT.getValue())
                    .add(MysqlKeywordEnum.editKeyword(MysqlKeywordEnum.CHARSET, alterEditInfo.getTableCharset()));
        }
        // 构建 DEFAULT COLLATE=XXX || COLLATE=XXX
        if (StrUtil.isNotBlank(alterEditInfo.getTableCollate())) {
            if (!alterPrefixJoiner.toString().contains(MysqlKeywordEnum.DEFAULT.getValue())) {
                alterPrefixJoiner.add(MysqlKeywordEnum.DEFAULT.getValue());
            }
            alterPrefixJoiner.add(MysqlKeywordEnum.editKeyword(MysqlKeywordEnum.COLLATE, alterEditInfo.getTableCollate()));
        }
        // 构建 COMMENT 'XX'
        if (StrUtil.isNotBlank(alterEditInfo.getTableComment())) {
            alterPrefixJoiner.add(MysqlKeywordEnum.comment(alterEditInfo.getTableComment()));
        }
        // 构建 AUTO_INCREMENT=xxx
        if (alterEditInfo.getAutoIncrement() != null) {
            alterPrefixJoiner.add(MysqlKeywordEnum.editKeyword(MysqlKeywordEnum.AUTO_INCREMENT, alterEditInfo.getAutoIncrement()));
        }
        return alterPrefixJoiner.toString();
    }

    /**
     * 构建 rename table ddl
     *
     * <p>RENAME TABLE testdata.test6 TO testdata.test7;
     *
     * @param alterEditInfo 修改信息
     * @return {@link String} RENAME TABLE xxx TO XXX ddl 语句
     */
    private String buildReTableDDl(AlterEditInfo alterEditInfo) {
        StringJoiner sj = new StringJoiner(CommonSymbol.SPACE, VariousStrPool.EMPTY, MysqlKeywordEnum.END.getValue());

        sj.add(MysqlKeywordEnum.RENAME.getValue())
                .add(MysqlKeywordEnum.TABLE.getValue())
                .add(getTable())
                .add(MysqlKeywordEnum.TO.getValue())
                .add(alterEditInfo.getTableRename());
        return sj.toString();
    }

    /**
     * 构建 CHANGE ddl
     *
     * <p>ALTER TABLE XXX CHANGE column_name re_column_name int(11);
     *
     * @param alterEditInfo 修改信息
     * @return {@link String} alter table xx change ddl 语句
     */
    private String buildChangeDDl(AlterEditInfo alterEditInfo) {
        String columnName = alterEditInfo.getColumnName();
        String tableColumnInfo = this.getTableColumnType(columnName);
        return this.getChangePrefixJoiner()
                .add(columnName)
                .add(alterEditInfo.getColumnRename())
                .add(tableColumnInfo).toString();
    }

    /**
     * 构建 modify ddl
     *
     * <p>ALTER TABLE XXX MODIFY column_name varchar(100) NULL COMMENT 'asd'; modify 必须声明 column_name
     *
     * @param alterEditInfo 修改信息
     * @return {@link String} alter table xx modify ddl 语句
     */
    private String buildModifyDDl(AlterEditInfo alterEditInfo) {
        // 必须声明字段名
        String columnName = alterEditInfo.getColumnName();
        // 获取字段原信息类型
        String tableColumnInfo = this.getTableColumnType(columnName);
        StringJoiner modifyPrefixJoiner = this.getModifyPrefixJoiner();
        modifyPrefixJoiner.add(columnName).add(tableColumnInfo);
        // 构建 DEFAULT xxx
        if (StrUtil.isNotBlank(alterEditInfo.getDefaultValue())) {
            modifyPrefixJoiner
                    .add(MysqlKeywordEnum.DEFAULT.getValue())
                    .add(MysqlKeywordEnum.value(alterEditInfo.getDefaultValue()));
        }
        // 构建 NULL 或 NOT NULL
        if (alterEditInfo.getAcceptNull() != null) {
            modifyPrefixJoiner.add(MysqlKeywordEnum.acceptNull(alterEditInfo.getAcceptNull()));
        }
        // 存在额外值
        if (StrUtil.isNotBlank(alterEditInfo.getExtra())) {
            modifyPrefixJoiner.add(alterEditInfo.getExtra());
        }
        // 构建 COMMENT 'XXX'
        if (StrUtil.isNotBlank(alterEditInfo.getComment())) {
            modifyPrefixJoiner.add(MysqlKeywordEnum.comment(alterEditInfo.getComment()));
        }
        return modifyPrefixJoiner.toString();
    }

    /**
     * 构建 ALTER TABLE XXX ADD column_name INT(11) DEFAULT 0 COMMENT 'XXX'
     *
     * @param alterAddInfo ALTER ADD 信息
     * @return {@link String} - 构建 add column ddl 语句
     */
    private String buildAddColumnDDl(AlterAddInfo alterAddInfo) {
        // 字段类型不能为空
        if (StrUtil.isBlank(alterAddInfo.getColumnType())) {
            throw new SqlBuilderException(SqlBuilderErrorMsg.COLUMN_TYPE_IS_EMPTY);
        }
        StringJoiner addJoiner = getAddPrefixJoiner();
        // ALTER TABLE table ADD `aaa` INT(11)
        addJoiner.add(MysqlKeywordEnum.quoteVal(alterAddInfo.getColumnName()))
                .add(alterAddInfo.getColumnType());
        if (alterAddInfo.getDefaultValue() != null) {
            // 存在指定默认值 DEFAULT XXX
            addJoiner.add(MysqlKeywordEnum.DEFAULT.getValue())
                    .add(MysqlKeywordEnum.value(alterAddInfo.getDefaultValue()));
        }
        // 指定额外值
        if (StrUtil.isNotBlank(alterAddInfo.getExtra())) {
            addJoiner.add(alterAddInfo.getExtra());
        }
        // 支持 null
        if (alterAddInfo.isAcceptNull()) {
            addJoiner.add(MysqlKeywordEnum.NULL.getValue());
        } else {
            // not null
            addJoiner.add(MysqlKeywordEnum.NOT_NULL.getValue());
        }
        // 判断是否有 comment 有的话追加 COMMENT 'XXX'
        if (StrUtil.isNotBlank(alterAddInfo.getComment())) {
            addJoiner.add(MysqlKeywordEnum.COMMENT.getValue())
                    .add(MysqlKeywordEnum.singeQuoteVal(alterAddInfo.getComment()));
        }
        return addJoiner.toString();
    }

    /**
     * 构建 ALTER TABLE XXX ADD CONSTRAINT foreign_key_name FOREIGN KEY(column_name) REFERENCES
     * references_database.references_table_name(references_column_name)
     *
     * @param alterAddInfo ALTER ADD 信息
     * @return {@link String} - 构建 foreign key ddl 语句
     */
    private String buildForeignDDl(AlterAddInfo alterAddInfo) {
        StringJoiner foreignJoiner = this.getAddPrefixJoiner();
        // 如果没有声明 foreign key column 那么默认指定当前 column
        List<String> foreignKeyColumnName = alterAddInfo.getForeignKeyColumnName();
        if (CollectionUtil.isEmpty(alterAddInfo.getForeignKeyColumnName())) {
            foreignKeyColumnName = Collections.singletonList(alterAddInfo.getColumnName());
        }

        if (alterAddInfo.getForeignKeyName() == null) {
            // 根据 foreign column 和 references column 生成指定的 foreign key name
            alterAddInfo.setForeignKeyName(
                    getTable()
                            + "_fk_"
                            + StrUtil.join(CommonSymbol.COMMA, foreignKeyColumnName)
                            + "_"
                            + StrUtil.join(CommonSymbol.COMMA, alterAddInfo.getReferencesColumnName())
            );
        }
        foreignJoiner.add(MysqlKeywordEnum.CONSTRAINT.getValue())
                .add(alterAddInfo.getForeignKeyName())
                .add(MysqlKeywordEnum.FOREIGN_KEY.getValue() + MysqlKeywordEnum.brackets(foreignKeyColumnName, true))
                .add(MysqlKeywordEnum.REFERENCES.getValue());
        // 查看是否指定数据库
        if (StrUtil.isNotBlank(alterAddInfo.getReferencesDatabase())) {
            foreignJoiner.add(alterAddInfo.getReferencesDatabase())
                    .add(".")
                    .add(alterAddInfo.getReferencesTable() + MysqlKeywordEnum.brackets(alterAddInfo.getReferencesColumnName(), true));
        } else {
            foreignJoiner.add(alterAddInfo.getReferencesTable() + MysqlKeywordEnum.brackets(alterAddInfo.getReferencesColumnName(), true));
        }
        // 查看是否有引用说明
        if (StrUtil.isNotBlank(alterAddInfo.getForeignKeyDeleteMethod())) {
            foreignJoiner.add(MysqlKeywordEnum.ON.getValue())
                    .add(MysqlKeywordEnum.DELETE.getValue())
                    .add(alterAddInfo.getForeignKeyDeleteMethod());
        }
        // 外键更新
        if (StrUtil.isNotBlank(alterAddInfo.getForeignKeyUpdateMethod())) {
            foreignJoiner.add(MysqlKeywordEnum.ON.getValue())
                    .add(MysqlKeywordEnum.UPDATE.getValue())
                    .add(alterAddInfo.getForeignKeyUpdateMethod());
        }
        return foreignJoiner.toString();
    }

    /**
     * 构建 ALTER TABLE XXX ADD CONSTRAINT constraint_name UNIQUE KEY(`column_name`) -- 追加约束还有 PRIMARY
     * KEY(`column_name`) * 以及 CHECK(表达式)类型
     *
     * @param alterAddInfo ALTER ADD 信息
     * @return {@link String} - 构建约束 ddl 语句
     */
    private String buildConstraintDDl(AlterAddInfo alterAddInfo) {
        StringJoiner constraintJoiner = this.getAddPrefixJoiner();
        String constraintColumnName = alterAddInfo.getConstraintColumnName();
        String constraintName = alterAddInfo.getConstraintName();
        String constraintType = alterAddInfo.getConstraintType();
        // 如果没有指定约束名称
        if (StrUtil.isBlank(constraintName)) {
            // 动态生成
            List<String> split = StrUtil.split(constraintType, " ");
            StringBuilder prefix = new StringBuilder("ck");
            for (String s : split) {
                if (!prefix.toString().equals("ck")) {
                    prefix.append(s.substring(0, 1).toLowerCase());
                } else {
                    prefix = new StringBuilder(s.substring(0, 1).toLowerCase());
                }
            }
            // 设置默认名称
            constraintName = prefix + "_" + constraintColumnName;
            alterAddInfo.setConstraintName(constraintName);
        }
        constraintJoiner.add(MysqlKeywordEnum.CONSTRAINT.getValue())
                .add(constraintName)
                .add(constraintType);
        if (constraintType.equals(MysqlConstraintTypeEnum.CHECK.getValue())) {
            constraintJoiner.add(MysqlKeywordEnum.bracket(alterAddInfo.getCheckExpressions()));
        } else {
            constraintJoiner.add(MysqlKeywordEnum.bracket(MysqlKeywordEnum.quoteVal(constraintColumnName)));
        }
        return constraintJoiner.toString();
    }

    /**
     * 返回 alter table xxx
     *
     * @return {@link StringJoiner}
     */
    private StringJoiner getAlterPrefixJoiner() {
        StringJoiner buildJoiner = new StringJoiner(CommonSymbol.SPACE, VariousStrPool.EMPTY, MysqlKeywordEnum.END.getValue());
        // ALTER TABLE table
        buildJoiner.add(MysqlKeywordEnum.ALTER.getValue())
                .add(MysqlKeywordEnum.TABLE.getValue())
                .add(this.getTable());
        return buildJoiner;
    }

    /**
     * 获得统一 add 前缀
     *
     * @return {@link StringJoiner} - 字符串构造器
     */
    private StringJoiner getAddPrefixJoiner() {
        StringJoiner addJoiner = this.getAlterPrefixJoiner();
        // ALTER TABLE table ADD aaa INT(11)
        addJoiner.add(MysqlKeywordEnum.ADD.getValue());
        return addJoiner;
    }

    /**
     * 获得统一 drop 前缀
     *
     * @return {@link StringJoiner} - 字符串构造器
     */
    private StringJoiner getDropPrefixJoiner() {
        StringJoiner dropJoiner = this.getAlterPrefixJoiner();
        // ALTER TABLE xxx DROP
        dropJoiner.add(MysqlKeywordEnum.DROP.getValue());
        return dropJoiner;
    }

    /**
     * 获得统一 modify 前缀
     *
     * @return {@link StringJoiner} - 字符串构造器
     */
    private StringJoiner getModifyPrefixJoiner() {
        StringJoiner modifyJoiner = this.getAlterPrefixJoiner();
        // ALTER TABLE table MODIFY
        modifyJoiner.add(MysqlKeywordEnum.MODIFY.getValue());
        return modifyJoiner;
    }

    /**
     * 获得统一 change 前缀
     *
     * @return {@link StringJoiner} - 字符串构造器
     */
    private StringJoiner getChangePrefixJoiner() {
        StringJoiner changeJoiner = this.getAlterPrefixJoiner();
        // ALTER TABLE table CHANGE
        changeJoiner.add(MysqlKeywordEnum.CHANGE.getValue());
        return changeJoiner;
    }

    /**
     * 获取表的字段类型
     *
     * @param columnName 字段名称
     * @return {@link String} - 字段类型
     */
    private String getTableColumnType(String columnName) {
        DevopsJdbc bean = SpringUtil.getBean(DevopsJdbc.class);
        SqlColumn tableColumnInfo = bean.getTableColumnInfo(super.getTable(), columnName);
        if (tableColumnInfo == null) {
            throw new SqlBuilderException(SqlBuilderErrorMsg.GET_PROGRAM_EXCEPTION);
        }
        return tableColumnInfo.getType();
    }
}
